|
|
|
When working with Excelfiles it is very common to know how many rows there is to process in the file so that You can inform the user in a progressbar or something. I've seen a numerous examples of simple looping through each row until the ActiveCell is a nullvalue (empty string). This is not very efficient if the file is very large. There is a shorter way, already given in the Excel object.
Dim FileName As String
Dim objExcel As Variant
Set objExcel = CreateObject("Excel.Application")
FileName = "C:\myExcelfile.xls"
Call objExcel.Workbooks.Open(FileName)
Dim RowNumber As Long
RowNumber = objExcel.Sheets(1).UsedRange.Rows.Count
MsgBox "Rowcount = " + CStr(RowNumber)
Call objExcel.ActiveWorkbook.Close
objExcel.Quit
Set objExcel = Nothing
It is simply as that...
Another tip on almost the same topic: In the same way as You can get the number of rows used, You can get the number of COLUMNS used by simply switching the "Rows" to "Columns":
ColumnNumber = objExcel.Sheets(1).UsedRange.Columns.Count
So, now You now exactly how many cells You have to work with... |